package com.ns002.yigou.quote.dao.impl;

import com.ns002.yigou.quote.entity.KLine;
import com.ns002.yigou.quote.entity.RealTimeQuote;
import com.opslab.util.DateUtil;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Repository;

import javax.persistence.EntityManager;
import javax.persistence.Query;
import java.util.Calendar;
import java.util.Date;
import java.util.List;

/**
 * @Author xu
 * @Date 2017/7/28 0028 上午 9:54
 * 执行原生sql
 */
@Repository
public class SqlExecuteDao {

    @Autowired
    EntityManager em;

    /**
     * 分表保存实时行情
     * @param realTimeQuote
     */
    public void saveRealTimeQuote(RealTimeQuote realTimeQuote){
        String tableName = getRealTimeQuoteTableName();

        String sql = "insert into "+ tableName +" (contract, quote, timestamp) values (:contract, :quote, :timestamp)";

        Query query = em.createNativeQuery(sql);
        query.setParameter("contract", realTimeQuote.getContract());
        query.setParameter("quote", realTimeQuote.getQuote());
        query.setParameter("timestamp", realTimeQuote.getTimestamp());

        query.executeUpdate();
    }

    /**
     * 一分钟K线
     * @param contract   行情标识
     * @param quoteDate  K线行情时间点
     * @return
     */
    public KLine findKLineNodeBy1M(String contract, Date quoteDate){
        long space = 60000;

        KLine kLine = findKlineNode(contract, quoteDate, new Date(quoteDate.getTime() + space));
        if(kLine == null){
            return null;
        }
        kLine.setType(1);

        return kLine;
    }

    /**
     * 五分钟K线
     * @param contract   行情标识
     * @param quoteDate  K线行情时间点
     * @return
     */
    public KLine findKLineNodeBy5M(String contract, Date quoteDate){
        long space = 60000 * 5;
        KLine kLine = findKlineNode(contract, quoteDate, new Date(quoteDate.getTime() + space));
        if(kLine == null){
            return null;
        }
        kLine.setType(2);

        return kLine;
    }

    /**
     * 十五分钟K线
     * @param contract   行情标识
     * @param quoteDate  K线行情时间点
     * @return
     */
    public KLine findKLineNodeBy15M(String contract, Date quoteDate){
        long space = 60000 * 15;

        KLine kLine = findKlineNode(contract, quoteDate, new Date(quoteDate.getTime() + space));
        if(kLine == null){
            return null;
        }
        kLine.setType(3);

        return kLine;
    }

    /**
     * 三十分钟K线
     * @param contract   行情标识
     * @param quoteDate  K线行情时间点
     * @return
     */
    public KLine findKLineNodeBy30M(String contract, Date quoteDate){
        long space = 60000 * 30;

        KLine kLine = findKlineNode(contract, quoteDate, new Date(quoteDate.getTime() + space));
        if(kLine == null){
            return null;
        }
        kLine.setType(4);

        return kLine;
    }

    /**
     * 一小时K线
     * @param contract   行情标识
     * @param quoteDate  K线行情时间点
     * @return
     */
    public KLine findKLineNodeBy1H(String contract, Date quoteDate){
        long space = 60000 * 60;

        KLine kLine = findKlineNode(contract, quoteDate, new Date(quoteDate.getTime() + space));
        if(kLine == null){
            return null;
        }
        kLine.setType(5);

        return kLine;
    }

    /**
     * 两小时K线
     * @param contract   行情标识
     * @param quoteDate  K线行情时间点
     * @return
     */
    public KLine findKLineNodeBy2H(String contract, Date quoteDate){
        long space = 60000 * 60 * 2;

        KLine kLine = findKlineNode(contract, quoteDate, new Date(quoteDate.getTime() + space));
        if(kLine == null){
            return null;
        }
        kLine.setType(6);

        return kLine;
    }

    /**
     * 四小时K线
     * @param contract   行情标识
     * @param quoteDate  K线行情时间点
     * @return
     */
    public KLine findKLineNodeBy4H(String contract, Date quoteDate){
        long space = 60000 * 60 * 4;

        KLine kLine = findKlineNode(contract, quoteDate, new Date(quoteDate.getTime() + space));
        if(kLine == null){
            return null;
        }
        kLine.setType(7);

        return kLine;
    }

    /**
     * 一天K线
     * @param contract   行情标识
     * @param quoteDate  K线行情时间点
     * @return
     */
    public KLine findKLineNodeBy1D(String contract, Date quoteDate){
        long space = 60000 * 60 * 24;

        KLine kLine = findKlineNode(contract, quoteDate, new Date(quoteDate.getTime() + space));
        if(kLine == null){
            return null;
        }
        kLine.setType(8);

        return kLine;
    }

    /**
     * 查找K线行情节点
     * @param contract   行情标识
     * @param quoteDate  K线时间
     * @param endDate    结束时间
     * @return
     */
    public KLine findKlineNode(String contract, Date quoteDate, Date endDate){

        String tableName = getRealTimeQuoteTableName();

        //返回数据顺序：最低、最高、开盘、收盘
        String sql = " (SELECT quote FROM " + tableName +" WHERE contract = :contract AND `timestamp` BETWEEN :start AND :end ORDER BY quote LIMIT 1) " +
                " UNION ALL " +
                " (SELECT quote FROM " + tableName +" WHERE contract = :contract AND `timestamp` BETWEEN :start AND :end ORDER BY quote DESC LIMIT 1) " +
                " UNION ALL " +
                " (SELECT quote FROM " + tableName +" WHERE contract = :contract AND `timestamp` BETWEEN :start AND :end ORDER BY `timestamp` LIMIT 1) " +
                " UNION ALL " +
                " (SELECT quote FROM " + tableName +" WHERE contract = :contract AND `timestamp` BETWEEN :start AND :end ORDER BY `timestamp` DESC LIMIT 1) ";

        Query query = em.createNativeQuery(sql);
        query.setParameter("contract", contract);
        query.setParameter("start", quoteDate.getTime());
        query.setParameter("end", endDate.getTime());

        List<Double> list = query.getResultList();

        if(list.size() == 0){
            return null;
        }

        KLine kLine = new KLine();
        kLine.setLow(list.get(0));
        kLine.setHigh(list.get(1));
        kLine.setOpen(list.get(2));
        kLine.setClose(list.get(3));
        kLine.setContract(contract);
        kLine.setQuoteDate(DateUtil.dateTime(endDate));
        kLine.setStartDate(endDate);

        return kLine;

    }

    /**
     * 获取实时行情表名
     * @return
     */
    private String getRealTimeQuoteTableName(){
        Calendar calendar = Calendar.getInstance();
        calendar.setTime(new Date());  //创建下一周的表
        //第几周
        int week = calendar.get(Calendar.WEEK_OF_YEAR);
        int year = calendar.get(Calendar.YEAR);

        return "mk_realtime_quote_" + year + "_" + week;  //动态数据插入
    }

}
